var express = require('express')
var mysql = require('mysql')

const xlsx = require('node-xlsx')
const fs = require('fs')

var formidable = require('formidable');
var path = require('path');

var student = require('../model/student')

var record = require('../model/record')


var router = express.Router()

var query = require('../model/query')

// 配置数据库
var mysql = require('mysql');
const { publicDecrypt } = require('crypto');
var pool = mysql.createPool({
    connectionLimit: 10,
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'school',
    timezone: "08:00" //从数据库获取的时间日期正常
});

var json = {
    target: [{
        id: 1,
        name: 'Mike',
        group: '1',
        class: '四一班'
    }, {
        id: '2',
        name: 'Jerry',
        value: '2',
        date: '四一班'
    }]
}

/*****************************************前台页面接口 */

router.get('/', function (req, res) {
    let sql = 'SELECT * FROM `student`'
    query(pool, sql).then(function (ret) {
        res.status(200).json({
            code: 0,
            msg: '获取小组信息成功',
            data: ret
        })
    }).catch(function (err) {
        res.status(500).json({
            code: 1,
            msg: err,
            data: ''
        })
    })
})

/**
 * 根据组号和课题id获取学生信息
 */
router.post('/', function (req, res) {
    // console.log(req)
    console.log(req.body)
    let json = req.body
    // let groupId = req.body.groupId
    // console.log('组号是', groupId)
    student.findStudentByGroupId(json, function (err, data) {
        if (err) {
            res.status(500).json({
                code: 1,
                msg: err,
                data: ''
            })
        } else {
            res.status(200).json({
                code: 0,
                msg: '获取学生数据成功',
                data: data
            })
        }
    })
})

/**
 * 根据课题id获取课题标题
 */
router.post('/getTopicTitle', function (req, res) {
    console.log(req.body)
    let topic = req.body
    let sql = 'SELECT topic FROM topic WHERE id=' + topic.topicId
    console.log(sql)
    query(pool, sql).then(function (ret) {
        console.log(ret)
        res.status(200).json({
            code: 0,
            msg: 'success',
            data: ret
        })
    }).catch(function (err) {
        res.status(500).json({
            code: 1,
            msg: err,
            data: ''
        })
    })
})

router.get('/group/getGroups', function (req, res) {
    let sql = 'SELECT * FROM `group`'
    query(pool, sql).then(function (ret) {
        res.status(200).json({
            code: 0,
            msg: '获取小组信息成功',
            data: ret
        })
    }).catch(function (err) {
        res.status(500).json({
            code: 1,
            msg: err,
            data: ''
        })
    })
})

/**
 * 学生学习情况记录修改
 */
router.post('/record/update', function (req, res) {
    let recordInfo = req.body
    console.log(recordInfo)
    record.update(recordInfo, function (err, data) {
        if (err) {
            console.log(err)
            return res.status(500).json({
                code: 1,
                msg: err,
                data: ''
            })
        }
        res.status(200).json({
            code: 0,
            msg: '保存成功',
            data: ''
        })
    })
})

router.post('/topic/add', function (req, res) {
    console.log(req.body)
    let topic = req.body.topic
    let sql = 'INSERT `topic`(topic) VALUE("' + topic + '")'
    console.log(sql)
    query(pool, sql).then(function (ret) {
        console.log(ret)
        res.status(200).json({
            code: 0,
            msg: 'success',
            data: ret
        })
    }).catch(function (err) {
        res.status(500).json({
            code: 1,
            msg: err,
            data: ''
        })
    })
})

router.get('/record/getLastTopic', function (req, res) {
    record.getLastTopic(function (err, data) {
        if (err) {
            return res.status(500).json({
                code: 1,
                msg: err,
                data: ''
            })
        }
        res.status(200).json({
            code: 0,
            msg: '获取最新课题数据成功',
            data: data
        })
    })
})



/**********************************后台管理页面接口 */




/**
 * 后台获取学生数据
 */
router.get('/admin', function (req, res) {
    let param = req.query
    console.log(req)
    //每页显示的数据数目
    let pageSize = 10
    console.log(param)
    let sql
    if (param.page) {
        let start = (param.page - 1) * pageSize
        sql = 'SELECT * FROM (`student` INNER join `scores` on student.sid=scores.studentId) INNER JOIN examination on scores.type = examination.id limit ' + start + ', ' + pageSize
    } else {
        sql = 'SELECT * FROM (`student` INNER join `scores` on student.sid=scores.studentId) INNER JOIN examination on scores.type = examination.id limit ' + pageSize
    }
    console.log('执行的sql语句是', sql)
    let pageNum = param.page ? param.page : 1
    console.log('分页是', pageNum)
    let json = {}
    query(pool, sql).then(function (data) {

        json.student = data
        sql = 'select count(*) from `student`'
        return query(pool, sql)
    }).then(function (data) {
        let count = data[0]['count(*)']
        let pageCount
        if (count % pageSize == 0) {
            pageCount = count / pageSize
        } else {
            pageCount = parseInt(count / pageSize) + 1
        }
        console.log('总页码数十', pageCount)
        res.render('index.html', {
            student: json.student,
            pageNum: pageNum,
            pageCount: pageCount
        })
    }).catch(function (e) {
        console.log(JSON.stringify(e))
        res.status(500).json({
            code: 2,
            msg: e
        })
    })
})

router.get('/student', function (req, res) {
    console.log(2333)
    res.render('dist/index.html')
})

router.get('/getGroup', function (req, res) {
    let arr = [{
        id: 1,
        name: '2333'
    }, {
        id: 2,
        name: '222'
    }]
    res.send(arr)
})

/**
 * 添加学生
 */
router.post('/student/new', function (req, res) {
    console.log(req.body)
    let studentInfo = req.body
    for (let key in studentInfo) {
        if (studentInfo[key] == '') {
            studentInfo[key] = null
        }
    }
    console.log('修改后的数据是', studentInfo)
    student.add(studentInfo, function (err, data) {
        if (err) {
            console.log(err)
            return res.status(500).send('add student fail')
        }
        res.redirect('/admin')
    })
})

/**
 * 修改学生信息
 */
router.post('/student/update', function (req, res) {
    console.log(req.body)
    let studentInfo = req.body
    for (let key in studentInfo) {
        if (studentInfo[key] == '') {
            studentInfo[key] = null
        }
    }
    student.update(studentInfo, function (err, data) {
        if (err) {
            return res.status(500).send('update student fail')
        }
        res.redirect('/admin')
    })
})

/**
 * 查询学生信息
 */
router.post('/student/search', function (req, res) {
    let studentInfo = req.body
    student.findStudent(studentInfo, function (err, data) {
        if (err) {
            return res.status(500).send('update student fail')
        }
        res.render('index.html', {
            student: data,
            pageNum: 1,
            pageCount: 1
        })
    })
})

/**
 * 上传Excel文件
 */
router.post('/upload', function (req, res) {
    console.log('上传文件')
    var form = new formidable.IncomingForm();   // 创建上传表单
    form.encoding = 'utf-8';                  // 设置编辑
    form.uploadDir = 'public/upload/';        // 设置上传目录
    form.keepExtensions = true;               // 保留后缀
    form.maxFieldsSize = 2 * 1024 * 1024;     // 文件大小（默认20M）

    // 1.上传Excel文件

    form.parse(req, function (err, fields, files) {
        console.log(err)
        if (err) {
            res.send({
                status: 201,
                message: err
            });
            return;
        };
        try {
            console.log(1111)
            // uploadFile = files.uploadFile.name
            console.log(files)
            var filename = files.uploadFile.originalFilename;
            console.log('文件名称是', filename)
            // 对文件名进行处理，以应对上传同名文件的情况
            var nameArray = filename.split('.');
            var type = nameArray[nameArray.length - 1];
            var name = '';
            for (var i = 0; i < nameArray.length - 1; i++) {
                name = name + nameArray[i];
            }
            var rand = Math.random() * 100 + 900;
            var num = parseInt(rand, 10);

            var avatarName = name + num + '.' + type;

            var newPath = form.uploadDir + avatarName;
            // 若文件流的键名为uploadFile，则fs.renameSync(files.uploadFile.path, newPath)

            // 2.重命名上传的文件,把文件存储在/public/upload目录下

            fs.renameSync(files.uploadFile.filepath, newPath);  //重命名

            // 3.读取上传的Excel文件

            // 解析得到文档中的所有 sheet
            let sheets = xlsx.parse(fs.readFileSync(newPath));

            // 遍历 sheet
            sheets.forEach(function (sheet) {
                let obj = {};
                let keys = [];
                let scores = []
                console.log(2333)
                console.log(sheet)
                // 遍历xlsx每行内容
                for (let rowId in sheet['data']) {
                    let row = sheet['data'][rowId];
                    if (rowId == 0) {
                        keys = row;
                    } else {
                        let item = {}
                        for (let i = 0; i < row.length; i++) {
                            obj[keys[i]] = row[i];
                            if (keys[i] == '姓名') {
                                item.name = row[i]
                            } else if (keys[i] == '语文') {
                                item.chinese = row[i]
                            } else if (keys[i] == '数学') {
                                item.math = row[i]
                            } else if (keys[i] == '英语') {
                                item.english = row[i]
                            } else if (keys[i] == '政治') {
                                item.politics = row[i]
                            } else if (keys[i] == '历史') {
                                item.history = row[i]
                            } else if (keys[i] == '地理') {
                                item.geography = row[i]
                            } else if (keys[i] == '物理') {
                                item.physics = row[i]
                            } else if (keys[i] == '化学') {
                                item.chemistry = row[i]
                            } else if (keys[i] == '生物') {
                                item.biology = row[i]
                            } else if (keys[i] == '总分') {
                                item.totalScore = row[i]
                            } else if (keys[i] == '班级') {
                                item.class = row[i]
                            } else if (keys[i] == '考试类型') {
                                item.type = row[i]
                            }
                        }
                        item.geography = item.geography == undefined ? null : item.geography
                        item.physics = item.physics == undefined ? null : item.physics
                        item.chemistry = item.chemistry == undefined ? null : item.chemistry
                        item.biology = item.biology == undefined ? null : item.biology
                        scores.push(item)
                    }
                }
                console.log(scores[0])

                // 4.把Excel文件的内容存储在数据库中
                for (let i = 0; i < scores.length; i++) {
                    student.add(scores[i], function (err, data) {
                        if (err) {
                            return res.status(500).send('update student fail')
                        }
                    })
                }

                res.status(200).json({
                    code: 0,
                    msg: '插入数据成功',
                    data: scores
                })
            });


            // res.send({ status: 200, message: '文件上传成功' });
        } catch (err) {
            console.log('出现问题')
            console.log(err)
            res.send({
                status: 201,
                message: err
            });
            return;
        }
    });
})

/**
 * Excel导入学生数据
 */

router.get('/upload/excel', function (req, res) {

    // 解析得到文档中的所有 sheet
    let sheets = xlsx.parse(fs.readFileSync('./scores.xlsx'));

    // 遍历 sheet
    sheets.forEach(function (sheet) {
        let obj = {};
        let keys = [];
        let scores = []
        console.log(2333)
        console.log(sheet)
        // 遍历xlsx每行内容
        for (let rowId in sheet['data']) {
            let row = sheet['data'][rowId];
            if (rowId == 0) {
                keys = row;
            } else {
                let item = {}
                for (let i = 0; i < row.length; i++) {
                    obj[keys[i]] = row[i];
                    if (keys[i] == '姓名') {
                        item.name = row[i]
                    } else if (keys[i] == '语文') {
                        item.chinese = row[i]
                    } else if (keys[i] == '数学') {
                        item.math = row[i]
                    } else if (keys[i] == '英语') {
                        item.english = row[i]
                    } else if (keys[i] == '政治') {
                        item.politics = row[i]
                    } else if (keys[i] == '历史') {
                        item.history = row[i]
                    } else if (keys[i] == '地理') {
                        item.geography = row[i]
                    } else if (keys[i] == '物理') {
                        item.physics = row[i]
                    } else if (keys[i] == '化学') {
                        item.chemistry = row[i]
                    } else if (keys[i] == '生物') {
                        item.biology = row[i]
                    } else if (keys[i] == '总分') {
                        item.totalScore = row[i]
                    } else if (keys[i] == '班级') {
                        item.class = row[i]
                    } else if (keys[i] == '考试类型') {
                        item.type = row[i]
                    }
                }
                item.geography = item.geography == undefined ? null : item.geography
                item.physics = item.physics == undefined ? null : item.physics
                item.chemistry = item.chemistry == undefined ? null : item.chemistry
                item.biology = item.biology == undefined ? null : item.biology
                scores.push(item)
            }
        }
        console.log(scores[0])
        for (let i = 0; i < scores.length; i++) {
            student.add(scores[i], function (err, data) {
                if (err) {
                    return res.status(500).send('update student fail')
                }
            })
        }

        res.status(200).json({
            code: 0,
            msg: '插入数据成功',
            data: scores
        })
    });
})

module.exports = router